-- @owner: @peilinqian
-- @date: 2024/10/22
-- @testpoint: 创建after触发器，insert触发事件的触发器，使用show triggers查询

@conn PrimaryDbBmode;

--step1：创建表 expect：成功
create table t_compatibility_grammar_show_case0006(id1 int, id2 int, id3 int) with (orientation = row);
create table t_compatibility_grammar_show_case0006_1(id1 int, id2 int, id3 int);

--step2：创建触发器函数 expect：成功
create or replace function f_compatibility_grammar_show_case0006() returns trigger as
$$
declare
begin
 insert into t_compatibility_grammar_show_case0006_1 values(new.id1, new.id2, new.id3);
 return new;
end
$$ language plpgsql;
/

--step3：创建after insert类型触发器 expect：成功
create constraint trigger trigger_compatibility_grammar_show_case0006 after insert on t_compatibility_grammar_show_case0006 for each row execute procedure f_compatibility_grammar_show_case0006();

--step4：执行insert触发事件 expect：成功
insert into t_compatibility_grammar_show_case0006 values(100,200,300);
select * from t_compatibility_grammar_show_case0006;
----检查触发结果
select * from t_compatibility_grammar_show_case0006_1;

--step5：查询触发器信息不加可选项 expect：显示结果正确
show triggers ;

--step6：查询触发器信息from schema； expect：显示结果正确
show triggers from public;

--step7：查询触发器信息添加where条件； expect：显示结果正确
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0006';
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0006' and "Event"='INSERT';
show triggers from public where "Trigger" like 'trigger_compatibility_grammar_show_case0006';

--step8：清理数据； expect：显示结果正确
drop trigger trigger_compatibility_grammar_show_case0006 on t_compatibility_grammar_show_case0006;
drop function f_compatibility_grammar_show_case0006();
drop table t_compatibility_grammar_show_case0006;
drop table t_compatibility_grammar_show_case0006_1;
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0006';